/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbAddressModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`addr_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '地址id',
  //`type` int(3) NOT NULL DEFAULT '0' COMMENT '地址类型：0用户地址管理, 1用户地址列表用, 10商品订单, 20发票订单',
  //`mobile` bigint(20) NOT NULL COMMENT '手机号',
  //`phone` bigint(20) DEFAULT NULL COMMENT '座机号',
  //`name` varchar(20) NOT NULL COMMENT '收货者',
  //`prov` bigint(20) DEFAULT '' COMMENT '省会',
  //`city` bigint(20) DEFAULT '' COMMENT '城市',
  //`dist` bigint(20) DEFAULT NULL COMMENT '区',
  //`detail` varchar(500) NOT NULL DEFAULT '' COMMENT '详细地址',
  //`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  //`default` int(11) NOT NULL DEFAULT '0' COMMENT '默认地址: 0否,1是',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`del_flg` int(1) NOT NULL DEFAULT '0',


};
ItbAddressModel.prototype = new ItbBaseModel();

// 取得表名
ItbAddressModel.prototype.getTableName = function(){
    return 'itb_address';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbAddressModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'addr_id='+this.addr_id;
};


// 根据用户id获取最新的用户地址信息
ItbAddressModel.prototype.getLastAddressByUserId = function(userId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " SELECT t.*, a1.name as prov_name, a2.name as city_name, a3.name as dist_name "+
            " FROM itb_address t, "+
            ' 	  itb_area a1, '+
            ' 	  itb_area a2, '+
            ' 	  itb_area a3 '+
            " WHERE t.user_id="+userId+" AND t.del_flg=0 AND t.type=0 "+
            ' AND t.prov = a1.id AND a1.del_flg=0 '+
            ' AND t.city = a2.id AND a2.del_flg=0 '+
            ' AND t.dist = a3.id AND a3.del_flg=0 '+
            " ORDER BY t.create_time desc limit 0,1 ";
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();
            if(error){
                console.log(error);
                return callback(error,null);
            }
            if(!results || results.length==0){
                return callback(null, null);
            }

            return callback(null, results[0]);
        });
    });
};

function sql_getAddrListByUser(bForCnt, userId, pageNum, pageSize){
    var sql = '';
    if(bForCnt) {
        sql += ' SELECT COUNT(*) AS count ';
    } else {
        sql += ' SELECT t.addr_id, t.type, t.mobile, t.phone, t.name, '+
            ' t.prov, t.city, t.dist, t.detail, t.user_id, t.default, '+
            ' a1.name as prov_name, a2.name as city_name, a3.name as dist_name ';
    }

    sql +=
        ' FROM itb_address t, '+
        ' 	  itb_area a1, '+
        ' 	  itb_area a2, '+
        ' 	  itb_area a3 '+
        ' WHERE t.del_flg=0 AND t.type=1 '+
        ' AND user_id='+userId+
        ' AND t.prov = a1.id AND a1.del_flg=0 '+
        ' AND t.city = a2.id AND a2.del_flg=0 '+
        ' AND t.dist = a3.id AND a3.del_flg=0 ';

    if(!bForCnt){
        //排序
        var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
        sql += " ORDER BY t.default DESC, t.update_time DESC "+// 默认地址放第一个
            " LIMIT "+skipCnt+", "+Number(pageSize||'0');
    }
    return sql;
};

// 根据用户id获取地址列表
ItbAddressModel.prototype.getAddrListByUserId = function(userId, pageNum, pageSize, callback){
    var THIS = this;
    pool.pool.getConnection(function(err, connection) {

        var sql = sql_getAddrListByUser(false, userId, pageNum, pageSize);
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();
            if(error){
                console.log(error);
                return callback(error,null);
            }
            return callback(null, results);
        });
    });
};
ItbAddressModel.prototype.getAddrListByUserIdCnt = function(userId, callback){
    var THIS = this;
    pool.pool.getConnection(function(err, connection) {

        var sql = sql_getAddrListByUser(true, userId, null, null);
        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();

            if(results && results.length > 0) {
                callback(error, results[0].count);
            }else {
                callback(error, 0);
            }
        });
    });
};


// 根据用户地址id获取地址信息
ItbAddressModel.prototype.getAddressByAddressId = function(addrId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql =
            ' SELECT addr.*, '+
            ' 	area1.name as prov_name,  '+
            ' 	area2.name as city_name,  '+
            ' 	area3.name as dist_name '+
            ' FROM itb_address addr, '+
            ' 	itb_area area1, '+
            ' 	itb_area area2, '+
            ' 	itb_area area3 '+
            ' WHERE addr.addr_id=  '+addrId+
            ' AND addr.del_flg=0  '+
            ' and addr.prov = area1.id '+
            ' and area1.del_flg=0 '+
            ' and addr.city = area2.id '+
            ' and area2.del_flg=0 '+
            ' and addr.dist = area3.id '+
            ' and area3.del_flg=0 ';

        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();
            if(error){
                console.log(error);
                return callback(error,null);
            }
            if(!results || results.length==0){
                return callback(null, null);
            }

            return callback(null, results[0]);
        });
    });
};


// 根据order id获取地址信息
ItbAddressModel.prototype.getAddressByOrderId = function(orderId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql =
            ' SELECT addr.*, '+
            ' 	area1.name as prov_name,  '+
            ' 	area2.name as city_name,  '+
            ' 	area3.name as dist_name '+
            ' FROM itb_trade_order o,'+
            '   itb_address addr, '+
            ' 	itb_area area1, '+
            ' 	itb_area area2, '+
            ' 	itb_area area3 '+
            ' WHERE o.del_flg=0 AND o.order_id='+orderId+
            ' AND addr.addr_id= o.addr_id '+
            ' AND addr.del_flg=0  '+
            ' and addr.prov = area1.id '+
            ' and area1.del_flg=0 '+
            ' and addr.city = area2.id '+
            ' and area2.del_flg=0 '+
            ' and addr.dist = area3.id '+
            ' and area3.del_flg=0 ';

        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();
            if(error){
                console.log(error);
                return callback(error,null);
            }
            if(!results || results.length==0){
                return callback(null, null);
            }

            return callback(null, results[0]);
        });
    });
};

// 根据发票id(inv_id)获取地址信息
ItbAddressModel.prototype.getAddressByInvId = function(invId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql =
            ' SELECT addr.*, '+
            ' 	area1.name as prov_name,  '+
            ' 	area2.name as city_name,  '+
            ' 	area3.name as dist_name '+
            ' FROM itb_invoice o,'+
            '   itb_address addr, '+
            ' 	itb_area area1, '+
            ' 	itb_area area2, '+
            ' 	itb_area area3 '+
            ' WHERE o.del_flg=0 '+
            ' AND o.inv_id='+invId+
            ' AND addr.addr_id= o.addr_id '+
            ' AND addr.del_flg=0  '+
            ' and addr.prov = area1.id '+
            ' and area1.del_flg=0 '+
            ' and addr.city = area2.id '+
            ' and area2.del_flg=0 '+
            ' and addr.dist = area3.id '+
            ' and area3.del_flg=0 ';

        console.log(sql);
        connection.query(sql, function (error, results) {
            connection.release();
            console.log();
            if(error){
                console.log(error);
                return callback(error,null);
            }
            if(!results || results.length==0){
                return callback(null, null);
            }

            return callback(null, results[0]);
        });
    });
};

/* 拷贝地址信息.
 * 参数:addressId:地址id
 * 参数：callback：回调，参数如下：
 *          error:错误消息
 *          newAddressId: 新地址id
 * */
ItbAddressModel.prototype.copyAddress = function(addressId, type, callback){

    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql =
            " INSERT INTO itb_address  "+
            " 		    (type, mobile, phone, name, prov, city, dist, detail, user_id, create_time, update_time, del_flg) "+
            " SELECT "+type+", mobile, phone, name, prov, city, dist, detail, user_id, create_time, update_time, del_flg "+
            " FROM itb_address "+
            " WHERE addr_id = "+addressId+
            " AND del_flg=0  ";

        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();
            if (error){
                console.log(error);
                return callback(error, null);
            }
            if(!result ){
                return callback(null, null);
            }

            callback(error, result.insertId);
        });
    });
};



/* 到“用户地址管理”表中查找是否有相同纪录，如果没有则拷贝地址到“用户地址管理”表
 * 参数:address:ItbAddressModel(地址信息)
 * 参数：callback：回调，参数如下：
 *          error:错误消息
 *          newAddressId: 新地址id
 * */
ItbAddressModel.prototype.insertUserAddressIfNotExist = function(address, callback){

    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " INSERT INTO itb_address "+
            " (type, mobile, phone, `name`, prov, city, dist, detail, user_id, del_flg, create_time, update_time ) "+
            " SELECT 0, "+
            address.mobile+", "+
            address.phone+", "+
            "'"+address.name+"', "+
            address.prov+", "+
            address.city+", "+
            address.dist+", "+
            "'"+address.detail+"', "+
            address.user_id+", "+
            "        0 ,"+
            "   sysdate(),sysdate() "+
            " FROM DUAL "+
            " WHERE NOT EXISTS "+
            "     (SELECT addr_id "+
            "      FROM itb_address "+
            "      WHERE TYPE=0 "+
            "        AND mobile= "+address.mobile+" "+
            "        AND phone"+(address.phone?("="+address.phone):" IS NULL ")+
            "        AND `name` "+(address.name?("='"+address.name+"'"):" IS NULL ")+
            "        AND prov= "+address.prov+
            "        AND city= "+address.city+
            "        AND dist= "+address.dist+
            "        AND detail "+(address.detail?("='"+address.detail+"'"):" IS NULL ")+
            "        AND user_id= "+address.user_id+
            "        AND del_flg=0 ) ";

        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();
            if (error){
                console.log(error);
                return callback(error, null);
            }
            if(!result ){
                return callback(null, null);
            }

            callback(error, result.insertId);
        });
    });
};

/**
 * 确认地址 id 数组中是否存在地址列表中的默认地址
 *
 * @param userId 用户唯一标识
 * @param ids 要查询的地址 id 集合, 用逗号拼接, 如: 123,321, 若为 null, 则查询用户的所有地址
 * @param callback 结果回调
 *              error 如果查询出错, 则为错误原因, 否为为 null
 *              id 如果查询结果有默认地址, 则为默认地址 id, 否则为 null
 * */
ItbAddressModel.prototype.checkUserDefaultAddress = function(userId, ids, callback) {
    var THIS = this;
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " SELECT * " +
            " FROM itb_address addr " +
            " WHERE addr.user_id= " + userId +
            "   AND addr.type=1 " +
            "   AND addr.default=1 " +
            "   AND del_flg=0 ";

        if(ids && ids.length>0) {
            sql += "   AND addr.addr_id IN ("+ids+") ";
        }

        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();

            if(error) {
                return callback(error, null);
            }

            if(result.length == 0) {
                return callback(null, null);
            }
            return callback(null, result[0].addr_id);
        });
    });

}

/**
 * 删除用户地址
 *
 * @param userId 用户唯一标识
 * @param ids 要删除的地址字符串, 用逗号拼接, 如: 123,321
 * @param callback 回调
 *           error: 删除是否成功
 * */
ItbAddressModel.prototype.removeUserAddress_v5_7 = function(userId, ids, callback) {
    var THIS = this;
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " UPDATE itb_address " +
            " SET del_flg=1, update_time=sysdate() " +
            " WHERE user_id= " + userId +
            "   AND addr_id IN (" + ids +") " +
            "   AND del_flg=0 " ;
        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();
            return callback(error);
        });
    });
};

/**
 * 设置用户的最新一条地址为默认地址
 *
 * @param userId 用户唯一标识
 * @param callback 回调
 *
 * 注意: 用户当前地址列表存在默认地址, 则放弃设置
 * */
ItbAddressModel.prototype.setUserLastAddressToDefault = function(userId, callback) {
    var THIS = this;
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " UPDATE itb_address addr " +
            " SET addr.default=1, addr.update_time=sysdate() " +
            " WHERE addr.type=1 " +
            "   AND addr.user_id=" + userId +
            "   AND addr.del_flg=0 " +
            "   AND addr.addr_id = " +
            "     (SELECT x.addr_id " +
            "      FROM " +
            "        (SELECT t.addr_id " +
            "         FROM itb_address t " +
            "         WHERE t.user_id=" + userId +
            "           AND t.type=1 " +
            "           AND t.del_flg=0 " +
            "         ORDER BY t.update_time DESC LIMIT 1) x) " +
            "   AND " +
            "     (SELECT count(*) " +
            "      FROM " +
            "        (SELECT * " +
            "         FROM itb_address c " +
            "         WHERE c.user_id=" + userId +
            "           AND c.type=1 " +
            "           AND c.default=1 " +
            "           AND c.del_flg=0) y)=0 " ;
        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();
            return callback(error);
        });
    });
};

/**
 * 取消用户当前的默认地址
 *
 * @param userId 用户唯一标识
 * @param callback 更新回调
 *             error 如果更新失败, 为失败原因
 * */

ItbAddressModel.prototype.setUserDefaultAddressToNormal = function(userId, callback) {
    var THIS = this;
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " UPDATE itb_address addr " +
            " SET addr.default=0, " +
            "     addr.update_time=sysdate() " +
            " WHERE addr.user_id=" + userId +
            "   AND addr.type=1 " +
            "   AND addr.default=1 " +
            "   AND addr.del_flg=0 " ;
        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();
            return callback(error);
        });
    });
};


// 导出
module.exports = ItbAddressModel;